<?php
include('connect_db.php');

/* IMPORTANT: Require a database called websysproject to work*/

/* drop tables*/

$query =
"DROP TABLE IF EXISTS bills";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS bills_users";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS categories";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS houses";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS houses_users";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS users";
$result = mysql_query("$query") or die(mysql_error());

$query =
"DROP TABLE IF EXISTS users_secure";
$result = mysql_query("$query") or die(mysql_error());

/* create tables*/
$query =
"CREATE TABLE IF NOT EXISTS bills (
        id int(11) NOT NULL AUTO_INCREMENT,
        house_id int(11) NOT NULL,
        user_id int(11) NOT NULL,
        amount int(11) NOT NULL,
        create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        category_id int(11) NOT NULL,
        notes varchar(300) NOT NULL,
        PRIMARY KEY (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
$result = mysql_query("$query") or die(mysql_error());

$query =
"CREATE TABLE IF NOT EXISTS bills_users (
          id int(11) NOT NULL AUTO_INCREMENT,
          bill_id int(11) NOT NULL,
          user_id int(11) NOT NULL,
          shared_scale double NOT NULL DEFAULT '0',
          if_paid tinyint(1) NOT NULL,
          PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
$result = mysql_query("$query") or die(mysql_error());

$query =
"CREATE TABLE IF NOT EXISTS categories (
          id int(11) NOT NULL AUTO_INCREMENT,
          name varchar(100) NOT NULL,
          PRIMARY KEY (id)
        ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21";
$result = mysql_query("$query") or die(mysql_error());

$query =
"CREATE TABLE IF NOT EXISTS houses (
          id int(11) NOT NULL AUTO_INCREMENT,
          name varchar(100) NOT NULL,
          address varchar(500) NOT NULL,
          create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
$result = mysql_query("$query") or die(mysql_error());

$query =
"CREATE TABLE IF NOT EXISTS houses_users (
          id int(11) NOT NULL AUTO_INCREMENT,
          house_id int(11) NOT NULL,
          user_id int(11) NOT NULL,
          privilege int(11) NOT NULL,
          PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
$result = mysql_query("$query") or die(mysql_error());

$query =
"CREATE TABLE IF NOT EXISTS users (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(100) NOT NULL,
  password varchar(200) NOT NULL,
  salt varchar(200) NOT NULL,
  is_admin tinyint(1) NOT NULL,
  lastname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  privilege int(11) NOT NULL DEFAULT '0',
  last_login timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4";
$result = mysql_query("$query") or die(mysql_error());

/*insert data*/

$query =
"INSERT INTO users (id, username, password, salt, is_admin, lastname, firstname, privilege, last_login) VALUES
(1, 'admin', '59c4ee49a5490f781a76558fd4a949925e66190c', '05da2b0abdcaa109a728fe17eaa47b70da5fffd7', 1, 'Admin_LastName', 'Admin_FirstName', 1, '2012-12-03 22:03:59'),
(4, 'kuov@rpi.edu', '7c8fbf590250a0f02f4cc7c35170f3f28e416cd9', '2617208618e30cbbcdb67c7c71b290f1806d6e48', 0, 'Kuo', 'Victoria', 0, '2012-12-04 16:51:52'),
(5, 'jinb@rpi.edu', '8cd371c7eb22e7a91008465ecb6c4cb1975e466e', 'f78e111cc45d3fa8780d96dd8782d550e2d7a0b6', 0, 'Jin', 'Bojiang', 0, '2012-12-04 17:08:47'),
(6, 'clarkb5@rpi.edu', '7a7e71beec9fae831fb0028299e77c8be11317d2', 'a6fe223aea7e10ce029c185ca3f44243498914d2', 0, 'Clark', 'Bethany', 0, '2012-12-04 16:52:59'),
(8, 'zhouz3@rpi.edu', '02f42270bb78b64f490ca57d208eadc30ca8a476', 'fae0cde226b5b749f4ad0cc72cd65d865379868b', 0, 'Zhou', 'Zhenzheng', 0, '2012-12-04 17:09:53')";
$result = mysql_query("$query") or die(mysql_error());

$query =
"INSERT INTO categories (id, name) VALUES
            (1, 'Rent'),
            (2, 'Electricity'),
            (3, 'Water'),
            (4, 'Gas'),
            (5, 'Cable'),
            (6, 'Food'),
            (7, 'House Supplies'),
            (8, 'Entertainment'),
            (9, 'Repair'),
            (10, 'Transportation'),
			      (11, 'Other')";
$result = mysql_query("$query") or die(mysql_error());

$query =
"INSERT INTO houses (id, name, address, create_time) VALUES
(3, 'We Love Web Systems Class', '76 13th Street, Troy NY, 12180', '2012-12-04 16:09:40'),
(4, 'Barton Hall: Room 103', '1999 Burdett Avenue, Troy NY, 12180', '2012-12-04 16:10:14')";
$result = mysql_query("$query") or die(mysql_error());

$query =
"INSERT INTO houses_users (id, house_id, user_id, privilege) VALUES
  (6, 3, 4, 2),
  (7, 3, 5, 1),
  (8, 3, 6, 2),
  (9, 3, 8, 2)";
$result = mysql_query("$query") or die(mysql_error());

$query =
"INSERT INTO bills (id, house_id, user_id, amount, create_time, category_id, notes) VALUES
  (13, 3, 5, 1500, '2012-12-04 16:37:51', 1, 'Rent for the month of May'),
  (14, 3, 5, 174, '2012-12-04 16:38:47', 2, 'Electricity for the month of May'),
  (15, 3, 5, 132, '2012-12-04 16:38:59', 3, 'Water for the month of May'),
  (16, 3, 4, 800, '2012-12-04 16:40:19', 10, 'AC broke called The AC Fixing Company to repair AC'),
  (17, 3, 4, 82, '2012-12-04 16:40:49', 6, 'Dinosaur BBQ on 5/3'),
  (18, 3, 4, 43, '2012-12-04 16:41:53', 6, 'Cheesecake Factory on 5/10'),
  (19, 3, 6, 87, '2012-12-04 16:42:26', 6, 'Groceries for the 1st week of May'),
  (20, 3, 6, 583, '2012-12-04 16:42:54', 6, 'Food supply for Backyard BBQ on 5/19 for 12 people'),
  (21, 3, 6, 147, '2012-12-04 16:43:08', 6, 'Groceries for the 3rd week of May'),
  (22, 3, 8, 64, '2012-12-04 16:45:05', 11, 'Gas to NYC'),
  (23, 3, 8, 10, '2012-12-04 16:45:22', 11, 'Gas to Price Chopper for the month of May'),
  (24, 3, 8, 78, '2012-12-04 17:09:50', 8, 'Birthday present for Bojiang'),
  (25, 3, 6, 39, '2012-12-04 17:10:22', 8, 'Birthday Cake for Bojiang')";
$result = mysql_query("$query") or die(mysql_error());

$query =
"INSERT INTO bills_users (id, bill_id, user_id, shared_scale, if_paid) VALUES
(7, 13, 4, 0.25, 0),
(8, 13, 6, 0.25, 0),
(9, 13, 8, 0.25, 0),
(10, 14, 4, 0.25, 0),
(11, 14, 6, 0.25, 0),
(12, 14, 8, 0.25, 0),
(13, 15, 4, 0.25, 0),
(14, 15, 6, 0.25, 0),
(15, 15, 8, 0.25, 0),
(16, 16, 5, 0.25, 0),
(17, 16, 6, 0.25, 0),
(18, 16, 8, 0.25, 0),
(19, 17, 5, 0.25, 0),
(20, 17, 6, 0.25, 0),
(21, 17, 8, 0.25, 0),
(22, 18, 6, 0.5, 0),
(23, 19, 4, 0.25, 0),
(24, 19, 5, 0.25, 0),
(25, 19, 8, 0.25, 0),
(26, 20, 4, 0.25, 0),
(27, 20, 5, 0.25, 0),
(28, 20, 8, 0.25, 0),
(29, 21, 4, 0.25, 0),
(30, 21, 5, 0.25, 0),
(31, 21, 8, 0.25, 0),
(32, 22, 4, 0.25, 0),
(33, 22, 5, 0.25, 0),
(34, 22, 6, 0.25, 0),
(35, 23, 4, 0.25, 0),
(36, 23, 5, 0.25, 0),
(37, 23, 6, 0.25, 0),
(38, 24, 4, 0.33333333333333, 0),
(39, 24, 6, 0.33333333333333, 0),
(40, 25, 4, 0.25, 0),
(41, 25, 5, 0.25, 0),
(42, 25, 8, 0.25, 0);";
$result = mysql_query("$query") or die(mysql_error());









echo "<p>Install finished.</p>";
?>
<a href="index.php">Go Home</a>